using System.Data;
using System.Data.SQLite;
using Model;

namespace DAL {
    public class UserDAL {
        private UserDAL() { }
        private static UserDAL obj = new UserDAL();
        public static UserDAL GetInstance() {
            return obj;
        }
        public User Login(User user) {
            SQLiteCommand sqLiteCommand = new SQLiteCommand("select * from userinfo where username = @uname and password = @pwd");
            sqLiteCommand.Parameters.AddWithValue("@uname", user.UserName);
            sqLiteCommand.Parameters.AddWithValue("@pwd", user.Password);

            SQLiteDataReader sqLiteDataReader = SqlHelper.GetDataReader(sqLiteCommand);

            if (sqLiteDataReader.Read()) {
                return new User() {
                    Uid = int.Parse(sqLiteDataReader["uid"].ToString()),
                    UserName = user.UserName,
                    Password = user.Password,
                    RealName = sqLiteDataReader["realName"].ToString(),
                    Position = sqLiteDataReader["position"].ToString(),
                };
            }
            return null;
        }

        //查询所有用户
        public DataTable SelectAllUsers() {
            return SqlHelper.GetDataTable(new SQLiteCommand("select * from userinfo"));
        }

        //新增用户
        public bool InsertUser(User user) {
            SQLiteCommand command =
                new SQLiteCommand(
                    "insert into userinfo(username, password, realname, position) values (@uname, @pwd, @realname, @position)");
            command.Parameters.AddWithValue("@uname", user.UserName);
            command.Parameters.AddWithValue("@pwd", user.Password);
            command.Parameters.AddWithValue("@realname", user.RealName);
            command.Parameters.AddWithValue("@position", user.Position);
            return SqlHelper.Update(command) == 1;
        }

        //更新用户信息
        public bool UpdateUserInfo(User user) {
            SQLiteCommand command =
                new SQLiteCommand(
                    "update userinfo set username = @uname, password = @pwd, realname = @realname, position = @position where uid = @uid");
            //"insert into userinfo(username, password, realname, position) valuse (@uname, @pwd, @realname, @position)"
            command.Parameters.AddWithValue("@uid", user.Uid);
            command.Parameters.AddWithValue("@uname", user.UserName);
            command.Parameters.AddWithValue("@pwd", user.Password);
            command.Parameters.AddWithValue("@realname", user.RealName);
            command.Parameters.AddWithValue("@position", user.Position);
            return SqlHelper.Update(command) == 1;
        }

        //通过id删除用户
        public bool DeleteById(User user) {
            SQLiteCommand command = new SQLiteCommand("delete from userinfo where uid = @uid");
            command.Parameters.AddWithValue("@uid", user.Uid);
            return SqlHelper.Update(command) == 1;
        }
    }
}